<?php

class M_student_payment_breakdown Extends MY_Model
{
	private $t = 'student_payment_breakdown';
	
	public function insert_payment_breakdown($data,$msp)
	{
		function d($x){return number_format(floatval($x),'2','.','');}
		function s($x){return str_replace(',','',$x);}
		$must_affected_rows = count($data);
		foreach($data as $k => $v)
		{
			$input[$k]['spb_amount_paid'] = d($v['amount_paid']);
			$input[$k]['spb_remaining_balance'] = d(s($v['remaining_balance']));
			$input[$k]['spb_fee_rate'] = d(s($v['fee_rate']));
			$input[$k]['spb_fee_name'] = $v['fee_name'];
			$input[$k]['spb_spr_id'] = $v['spr_id'];
			$input[$k]['spb_is_tuition_fee'] = isset($v['is_tuition_fee']) == NULL ? 0 : $v['is_tuition_fee'];
			$input[$k]['spb_is_misc_fee'] = isset($v['is_misc_fee']) == NULL ? 0 : $v['is_misc_fee'];
			$input[$k]['spb_is_other_fee'] = isset($v['is_other_fee'])  == NULL ? 0 : $v['is_other_fee'];
			$input[$k]['spb_is_elective_fee'] = isset($v['is_elective_fee'])  == NULL ? 0 : $v['is_elective_fee'];
			$input[$k]['spb_is_uniform_fee'] = isset($v['is_uniform_fee'])  == NULL ? 0 : $v['is_uniform_fee'];
			$input[$k]['spb_created'] = NOW;
			$input[$k]['spb_transacted_by'] = $this->c_user->userid;
			$input[$k]['spb_stud_enrollment_id'] = $v['stud_enrollment_id'];
			$input[$k]['spb_sef_id'] = $v['key_id'];
			$input[$k]['spb_sy_id'] = $this->c_sy->id;
			$sum[] =$v['amount_paid'];
		}
		
		$total = array_sum($sum);
		
		if($total == $msp)
		{
			$this->db->insert_batch($this->t,$input);
			$true = array('status'=>true,'log'=>'Payment record added successfully.');
			$false = array('status'=>false,'log'=>'An error was encountered while adding data to record.');
			return $this->db->affected_rows() == $must_affected_rows ? (object)$true : (object)$false;
		}else{
			$return['status'] = FALSE;
			$return['log']	  = 'Payment breakdown must equal amount paid by student';
			return (object)$return;
		}
	}
	
	
	public function get_payment_breakdown_by_spr_id($id)
	{
		if(is_numeric($id))
		{
			$id = intval($id);
			
			$sql = "SELECT `spb`.`spb_id`,
							`spb`.`spb_fee_name`,
							`spb`.`spb_fee_rate`,
							`spb`.`spb_amount_paid`,
							`spb`.`spb_remaining_balance`,
							`spb`.`spb_is_tuition_fee`,
							`spb`.`spb_is_other_fee`,
							`spb`.`spb_is_elective_fee`,
							`spb`.`spb_is_uniform_fee`,
							`spb`.`spb_is_misc_fee`,
							`spb`.`spb_is_cca_fee`,
							`spr`.`spr_or_no`
					FROM student_payment_breakdown spb
					LEFT JOIN student_payment_records spr ON spb.spb_spr_id = spr.spr_id
					WHERE spb_spr_id = ? 
					AND spb_sy_id = ?
					ORDER BY spb_is_tuition_fee,
					spb_is_misc_fee ASC";
			$query = $this->db->query($sql,array($id,$this->c_sy->id));
			return $query->num_rows() >=1 ? $query->result() : FALSE;
		}else{
			return FALSE;
		}
	}

	public function get_payment_breakdown_by_spr_id_with($id,$start,$limit)
	{
		if(is_numeric($id))
		{
			$id = intval($id);
			
			$sql = "SELECT `spb`.`spb_id`,
							`spb`.`spb_fee_name`,
							`spb`.`spb_fee_rate`,
							`spb`.`spb_amount_paid`,
							`spb`.`spb_remaining_balance`,
							`spb`.`spb_is_tuition_fee`,
							`spb`.`spb_is_other_fee`,
							`spb`.`spb_is_elective_fee`,
							`spb`.`spb_is_uniform_fee`,
							`spb`.`spb_is_misc_fee`,
							`spr`.`spr_or_no`
					FROM student_payment_breakdown spb
					LEFT JOIN student_payment_records spr ON spb.spb_spr_id = spr.spr_id
					WHERE spb_spr_id = ? 
					AND spb_sy_id = ?
					AND spb_amount_paid > 0
					ORDER BY spb_is_tuition_fee,
					spb_is_misc_fee ASC
					LIMIT {$start} , {$limit}";
			$query = $this->db->query($sql,array($id,$this->c_sy->id));
			return $query->num_rows() >=1 ? $query->result() : FALSE;
		}else{
			return FALSE;
		}
	}

	public function count_payment_breakdown_by_spr_id($id)
	{
		$sql = "SELECT COUNT(spb_id) AS Max_num 
				FROM student_payment_breakdown 
				WHERE spb_spr_id = ? 
				AND spb_sy_id = ?
				AND spb_amount_paid > 0";

		$query = $this->db->query($sql,array($id,$this->c_sy->id));
		return $query->num_rows() >=1 ? $query->row() : FALSE;
	}
	
	public function get_specific_payment_breakdown_by_spr_id($id,$type = FALSE)
	{
		if(is_numeric($id) AND $type !== FALSE)
		{
			$id = intval($id);
			
			switch(strtolower($type))
			{
				case $type == 'tuit':
					$this->db->where('spb_is_tuition_fee',1);
				break;
				case  $type == 'other':
					$this->db->where('spb_is_other_fee',1);
				break;
				case  $type == 'elec':
					$this->db->where('spb_is_elective_fee',1);
				break;
				case  $type == 'cca':
					$this->db->where('spb_is_cca_fee',1);
				break;
				case  $type == 'misc':
					$this->db->where('spb_is_misc_fee',1);
				break;
			}
			
			
			$this->db->select('`spb`.`spb_id`,
							`spb`.`spb_fee_name`,
							`spb`.`spb_fee_rate`,
							`spb`.`spb_amount_paid`,
							`spb`.`spb_remaining_balance`,
							`spb`.`spb_is_tuition_fee`,
							`spb`.`spb_is_other_fee`,
							`spb`.`spb_is_elective_fee`,
							`spb`.`spb_is_cca_fee`,
							`spb`.`spb_is_misc_fee`,
							`spr`.`spr_or_no`')
					 ->join('student_payment_records spr','spb.spb_spr_id = spr.spr_id','left')
					 ->where('spb_spr_id',$id)
					 ->where('spb_sy_id',$this->c_sy->id)
					 ->order_by('spb_is_tuition_fee,spb_is_misc_fee,spb_is_cca_fee ASC');
					 
					 
			$query = $this->db->get('student_payment_breakdown spb');
			return $query->num_rows() >=1 ? $query->result() : FALSE;
		}else{
			return FALSE;
		}
	}
	
	public function get_specific_breakdown($pid,$eid,$type = '')
	{
		$count_active_payment_record = "SELECT count(spr_id) as total 
										FROM student_payment_records
										WHERE spr_enrollment_id = ? 
										AND spr_is_deleted = 0 ";
		$count_result = $this->db->query($count_active_payment_record,array($eid));
		
		
		if($count_result->row()->total >= 1)
		{
			$sqlbalance = "SELECT spb_fee_rate as sef_fee_rate,spb_fee_name as sef_fee_name,
					spb_id,spb_amount_paid
					FROM student_payment_breakdown
					WHERE spb_spr_id = ? ";
							
			if($type == 'misc')
			{
				$sqlbalance .= " AND spb_is_misc_fee = 1 ";
				$counter = rand(1,99);
			}
			elseif($type == 'reg')
			{
				$sqlbalance .= " AND spb_is_registration_fee = 1 ";
				$counter = rand(1000,9999);
			}
			elseif($type == 'tuit')
			{
				$sqlbalance .= " AND spb_is_tuition_fee = 1 ";
				$counter = rand(10000,99999);
			}
			elseif($type == 'cca')
			{
				$sqlbalance .= " AND spb_is_cca_fee = 1 ";
				$counter = rand(10000,99999);
			}
			elseif($type == 'other')
			{
				$sqlbalance .= " AND spb_is_other_fee = 1 ";
				$counter = rand(10000,99999);
			}
			
			$balance = $this->db->query($sqlbalance,array($pid));
			return $balance->result();
		}else{
			return FALSE;
		}
		
	}
	
	public function update_payment_breakdown($data,$pid,$eid)
	{
		foreach($data as $key => $value)
		{
			$input[$key]['spb_amount_paid'] = $value['amount_paid'];
			$input[$key]['spb_remaining_balance'] = $value['remaining_balance'];
			$input[$key]['spb_updated'] = NOW;
			$input[$key]['spb_id'] = $value['spb_id'];
		}
		$row = count($input);
		$this->db->update_batch($this->t,$input,'spb_id');
		if( $this->db->affected_rows() >= $row )
		{
			return TRUE;
		}else{
			function d($x){return number_format(floatval($x),'2','.','');}
			function s($x){return str_replace(',','',$x);}
			$must_affected_rows = count($data);
			foreach($data as $k => $v)
			{
				$input[$k]['spb_amount_paid'] = d($v['amount_paid']);
				$input[$k]['spb_remaining_balance'] = d(s($v['remaining_balance']));
				$input[$k]['spb_fee_rate'] = d(s($v['fee_rate']));
				$input[$k]['spb_fee_name'] = $v['fee_name'];
				$input[$k]['spb_spr_id'] = $v['spr_id'];
				$input[$k]['spb_is_tuition_fee'] = isset($v['is_tuition_fee']) == NULL ? 0 : $v['is_tuition_fee'];
				$input[$k]['spb_is_misc_fee'] = isset($v['is_misc_fee']) == NULL ? 0 : $v['is_misc_fee'];
				$input[$k]['spb_is_other_fee'] = isset($v['is_other_fee'])  == NULL ? 0 : $v['is_other_fee'];
				$input[$k]['spb_is_elective_fee'] = isset($v['is_elective_fee'])  == NULL ? 0 : $v['is_elective_fee'];
				$input[$k]['spb_is_uniform_fee'] = isset($v['is_uniform_fee'])  == NULL ? 0 : $v['is_uniform_fee'];
				$input[$k]['spb_created'] = NOW;
				$input[$k]['spb_transacted_by'] = $this->c_user->userid;
				$input[$k]['spb_stud_enrollment_id'] = $v['stud_enrollment_id'];
				$input[$k]['spb_sef_id'] = $v['key_id'];
				$input[$k]['spb_sy_id'] = $this->c_sy->id;
				$sum[] =$v['amount_paid'];
			}
			

				$this->db->insert_batch($this->t,$input);
				$true = array('status'=>true,'log'=>'Payment record added successfully.');
				$false = array('status'=>false,'log'=>'An error was encountered while adding data to record.');
				return $this->db->affected_rows() == $must_affected_rows ? (object)$true : (object)$false;
		
		}
	}






}
